﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

using System.Xml;
using SMSSnew.smss_web_reference;
using SMSSnew.CLASSES;

namespace SMSSnew.DATA
{
    public class substituteDA
    {
        smss_web_service_app srv;
        DataTable mail, dt, noofteachers, dtsubjectTypeID, values_S_T_A_T, noOfslots, row, assignedTeachers, classid, dtrepalcingteacherdetails,
       checkedrecord, mailID;
        readXML XMLReader;




        public DataTable getAbsentTeachersDetails(string day)//returns a datatable of absent teachers and their details
        {
            string query = "";
            int from = 1, to = 8, halfEveFrom = 5, halfEveTo = 8, halfMorFrom = 1, halfMorTo = 4;
            int addingValue;

            switch (day)
            {
                case "Monday":
                    {
                        addingValue = 0; break;
                    }
                case "Tuesday":
                    {
                        addingValue = 8; break;
                    }
                case "Wednesday":
                    {
                        addingValue = 16; break;
                    }
                case "Thursday":
                    {
                        addingValue = 24; break;
                    }
                case "Sunday":
                    {
                        addingValue = 32; break;
                    }
                default:
                    {
                        addingValue = 0; break;
                    }
            }

            try
            {



                query = XMLReader.readXMLtxt("absentTeachers", "~/DATA/XML/substituteXML.xml");

                query = query.Replace("@from", (from + addingValue).ToString());
                query = query.Replace("@to", (to + addingValue).ToString());
                query = query.Replace("@a", (halfEveFrom + addingValue).ToString());
                query = query.Replace("@b", (halfEveTo + addingValue).ToString());
                query = query.Replace("@c", (halfMorFrom + addingValue).ToString());
                query = query.Replace("@d", (halfMorTo + addingValue).ToString());

                dt = srv.Search(query);

            }
            catch (Exception e)
            {
                throw e;
            }
            return dt;
        }

        public DataTable dataTableAbsentTeachersTeacherIDandSlots(string day)
        {
            string query = "";
            int from = 1, to = 8, halfEveFrom = 5, halfEveTo = 8, halfMorFrom = 1, halfMorTo = 4;
            int addingValue;

            switch (day)
            {
                case "Monday":
                    {
                        addingValue = 0; break;
                    }
                case "Tuesday":
                    {
                        addingValue = 8; break;
                    }
                case "Wednesday":
                    {
                        addingValue = 16; break;
                    }
                case "Thursday":
                    {
                        addingValue = 24; break;
                    }
                case "Sunday":
                    {
                        addingValue = 32; break;
                    }
                default:
                    {
                        addingValue = 0; break;
                    }
            }

            try
            {



                query = XMLReader.readXMLtxt("absentTeachersteacheridslotid", "~/DATA/XML/substituteXML.xml");

                query = query.Replace("@from", (from + addingValue).ToString());
                query = query.Replace("@to", (to + addingValue).ToString());
                query = query.Replace("@evefrom", (halfEveFrom + addingValue).ToString());
                query = query.Replace("@eveto", (halfEveTo + addingValue).ToString());
                query = query.Replace("@morfrom", (halfMorFrom + addingValue).ToString());
                query = query.Replace("@morto", (halfMorTo + addingValue).ToString());

                dt = srv.Search(query);

            }
            catch (Exception e)
            {
                throw e;
            }
            return dt;
        }

        public DataTable getFulldayPresentTeachersTeacherID(string day)
        {
            string query = "";
            switch (day)
            {
                case "Monday": break;

                case "Tuesday": break;

                case "Wednesday": break;

                case "Thursday": break;

                case "Sunday": break;

                default: break;

            }

            try
            {
                query = XMLReader.readXMLtxt("fulldaypresentTeacherTeacherID", "~/DATA/XML/substituteXML.xml");
                dt = srv.Search(query);
            }
            catch (Exception e)
            {
                throw e;
            }
            return dt;
        }


        public DataTable getNoOfFulldayPresentTeachers(string day)
        {
            string query = "";

            switch (day)
            {
                case "Monday": break;

                case "Tuesday": break;

                case "Wednesday": break;

                case "Thursday": break;

                case "Sunday": break;

                default: break;

            }

            try
            {
                query = XMLReader.readXMLtxt("nooffulldaypresentteachers", "~/DATA/XML/substituteXML.xml");
                noofteachers = srv.Search(query);
            }
            catch (Exception e)
            {
                throw e;
            }
            return noofteachers;
        }


        public DataTable getFulldayPresentTeachersSlots(string day, string teacherID)
        {
            string query = "";
            int from = 1, to = 8;
            int addingValue;

            switch (day)
            {
                case "Monday":
                    {
                        addingValue = 0; break;
                    }
                case "Tuesday":
                    {
                        addingValue = 8; break;
                    }
                case "Wednesday":
                    {
                        addingValue = 16; break;
                    }
                case "Thursday":
                    {
                        addingValue = 24; break;
                    }
                case "Sunday":
                    {
                        addingValue = 32; break;
                    }
                default:
                    {
                        addingValue = 0; break;
                    }
            }

            try
            {
                query = XMLReader.readXMLtxt("fulldaypresentTeachersSlots", "~/DATA/XML/substituteXML.xml");

                query = query.Replace("@from", (from + addingValue).ToString());
                query = query.Replace("@to", (to + addingValue).ToString());
                query = query.Replace("@teacher_id", teacherID);


                dt = srv.Search(query);

            }
            catch (Exception e)
            {
                throw e;
            }
            return dt;
        }

        public DataTable getHalfdayMorPresentTeachersTeacherID(string day)
        {
            string query = "";
            switch (day)
            {
                case "Monday": break;

                case "Tuesday": break;

                case "Wednesday": break;

                case "Thursday": break;

                case "Sunday": break;

                default: break;

            }

            try
            {
                query = XMLReader.readXMLtxt("halfdayMorpresentTeacherTeacherID", "~/DATA/XML/substituteXML.xml");
                dt = srv.Search(query);
            }
            catch (Exception e)
            {
                throw e;
            }
            return dt;
        }

        public DataTable getNoOfHalfMorPresentTeachers(string day)
        {

            string query = "";

            switch (day)
            {
                case "Monday": break;

                case "Tuesday": break;

                case "Wednesday": break;

                case "Thursday": break;

                case "Sunday": break;

                default: break;

            }

            try
            {
                query = XMLReader.readXMLtxt("noofhalfMorpresentteachers", "~/DATA/XML/substituteXML.xml");
                noofteachers = srv.Search(query);
            }
            catch (Exception e)
            {
                throw e;
            }
            return noofteachers;
        }

        public DataTable getHalfdayMorPresentTeachersSlots(string day, string teacherID)
        {
            string query = "";
            int from = 1, to = 4;
            int addingValue;

            switch (day)
            {
                case "Monday":
                    {
                        addingValue = 0; break;
                    }
                case "Tuesday":
                    {
                        addingValue = 8; break;
                    }
                case "Wednesday":
                    {
                        addingValue = 16; break;
                    }
                case "Thursday":
                    {
                        addingValue = 24; break;
                    }
                case "Sunday":
                    {
                        addingValue = 32; break;
                    }
                default:
                    {
                        addingValue = 0; break;
                    }
            }

            try
            {
                query = XMLReader.readXMLtxt("halfdayMorpresentTeachersSlots", "~/DATA/XML/substituteXML.xml");

                query = query.Replace("@from", (from + addingValue).ToString());
                query = query.Replace("@to", (to + addingValue).ToString());
                query = query.Replace("@teacher_id", teacherID);


                dt = srv.Search(query);

            }
            catch (Exception e)
            {
                throw e;
            }
            return dt;
        }

        public DataTable getHalfdayEvePresentTeachersTeacherID(string day)
        {
            string query = "";
            switch (day)
            {
                case "Monday": break;

                case "Tuesday": break;

                case "Wednesday": break;

                case "Thursday": break;

                case "Sunday": break;

                default: break;

            }

            try
            {
                query = XMLReader.readXMLtxt("halfdayEvepresentTeacherTeacherID", "~/DATA/XML/substituteXML.xml");
                dt = srv.Search(query);
            }
            catch (Exception e)
            {
                throw e;
            }
            return dt;
        }

        public DataTable getNoOfHalfEvePresentTeachers(string day)
        {

            string query = "";

            switch (day)
            {
                case "Monday": break;

                case "Tuesday": break;

                case "Wednesday": break;

                case "Thursday": break;

                case "Sunday": break;

                default: break;

            }

            try
            {
                query = XMLReader.readXMLtxt("noofhalfEvepresentteachers", "~/DATA/XML/substituteXML.xml");
                noofteachers = srv.Search(query);
            }
            catch (Exception e)
            {
                throw e;
            }
            return noofteachers;
        }


        public DataTable getHalfdayEvePresentTeachersSlots(string day, string teacherID)
        {
            string query = "";
            int from = 5, to = 8;
            int addingValue;

            switch (day)
            {
                case "Monday":
                    {
                        addingValue = 0; break;
                    }
                case "Tuesday":
                    {
                        addingValue = 8; break;
                    }
                case "Wednesday":
                    {
                        addingValue = 16; break;
                    }
                case "Thursday":
                    {
                        addingValue = 24; break;
                    }
                case "Sunday":
                    {
                        addingValue = 32; break;
                    }
                default:
                    {
                        addingValue = 0; break;
                    }
            }

            try
            {
                query = XMLReader.readXMLtxt("halfdayEvepresentTeachersSlots", "~/DATA/XML/substituteXML.xml");

                query = query.Replace("@from", (from + addingValue).ToString());
                query = query.Replace("@to", (to + addingValue).ToString());
                query = query.Replace("@teacher_id", teacherID);


                dt = srv.Search(query);

            }
            catch (Exception e)
            {
                throw e;
            }
            return dt;
        }

        public DataTable getshortleavePresentTeacherID()
        {
            string query = "";


            try
            {
                query = XMLReader.readXMLtxt("shortleavepresentTeacherTeacherID", "~/DATA/XML/substituteXML.xml");
                dt = srv.Search(query);
            }
            catch (Exception e)
            {
                throw e;
            }
            return dt;
        }

        public DataTable getshortleaveTeachersworkingSlots(string teacher_id, string day)
        {
            string query = "";
            int from = 1, to = 8;
            int addingValue;

            switch (day)
            {
                case "Monday":
                    {
                        addingValue = 0; break;
                    }
                case "Tuesday":
                    {
                        addingValue = 8; break;
                    }
                case "Wednesday":
                    {
                        addingValue = 16; break;
                    }
                case "Thursday":
                    {
                        addingValue = 24; break;
                    }
                case "Sunday":
                    {
                        addingValue = 32; break;
                    }
                default:
                    {
                        addingValue = 0; break;
                    }
            }

            try
            {



                query = XMLReader.readXMLtxt("shortleavepresentTeachersSlots", "~/DATA/XML/substituteXML.xml");

                query = query.Replace("@from", (from + addingValue).ToString());
                query = query.Replace("@to", (to + addingValue).ToString());
                query = query.Replace("@teacher_id", teacher_id);
                dt = srv.Search(query);


            }
            catch (Exception e)
            {
                throw e;
            }
            return dt;
        }
        public DataTable getNoOfShortLeavePresentTeachers(string day)
        {

            string query = "";

            switch (day)
            {
                case "Monday": break;

                case "Tuesday": break;

                case "Wednesday": break;

                case "Thursday": break;

                case "Sunday": break;

                default: break;

            }

            try
            {
                query = XMLReader.readXMLtxt("noofshortLeavepresentteachers", "~/DATA/XML/substituteXML.xml");
                noofteachers = srv.Search(query);
            }
            catch (Exception e)
            {
                throw e;
            }
            return noofteachers;
        }


        public DataTable getAbsentTimePeriod(string teacher_id)
        {
            string query = "";


            try
            {
                query = XMLReader.readXMLtxt("shortLeaveTeachersAbsentTimePeriod", "~/DATA/XML/substituteXML.xml");
                query = query.Replace("@teacher_id", teacher_id);
                dt = srv.Search(query);
            }
            catch (Exception e)
            {
                throw e;
            }
            return dt;


        }

        public void insertassignedteachers(string absentteacherID, string slotID, string dateAsString, string replacingteacherID)
        {

            string query = "";
            try
            {
                query = XMLReader.readXMLtxt("insertintosubstituteallocation", "~/DATA/XML/substituteXML.xml");
                query = query.Replace("@absent_teacher_id", absentteacherID);
                query = query.Replace("@slot_id", slotID);
                query = query.Replace("@date_assigned", dateAsString);
                query = query.Replace("@replacing_teacher_id", replacingteacherID);

                srv.Modify(query);
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public DataTable getsubject_type_ID_AbsentT(string teacherIDAbsentT)
        {

            string query = "";
            try
            {
                query = XMLReader.readXMLtxt("TeachersubjectTypeID", "~/DATA/XML/substituteXML.xml");
                query = query.Replace("@teacherid", teacherIDAbsentT);

                dtsubjectTypeID = srv.Search(query);
            }
            catch (Exception e)
            {
                throw e;
            }
            return dtsubjectTypeID;
        }

        public DataTable getsubject_type_ID_PresentT(string teacherIDPresentT)
        {

            string query = "";
            try
            {
                query = XMLReader.readXMLtxt("TeachersubjectTypeID", "~/DATA/XML/substituteXML.xml");
                query = query.Replace("@teacherid", teacherIDPresentT);

                dtsubjectTypeID = srv.Search(query);
            }
            catch (Exception e)
            {
                throw e;
            }
            return dtsubjectTypeID;
        }


        public DataTable getallvaluesofS_T_A_table()
        {
            string query = "";
            try
            {
                query = XMLReader.readXMLtxt("selectvaluesinSub_T_A_TAble", "~/DATA/XML/substituteXML.xml");

                values_S_T_A_T = srv.Search(query);
            }
            catch (Exception e)
            {
                throw e;
            }
            return values_S_T_A_T;
        }

        public DataTable getSubstituteTeachers(string dateAsStringg)
        {
            string query = "";
            try
            {
                query = XMLReader.readXMLtxt("selectallassignedTechrs", "~/DATA/XML/substituteXML.xml");
                query = query.Replace("@date_assigned", dateAsStringg);

                assignedTeachers = srv.Search(query);
            }
            catch (Exception e)
            {
                throw e;
            }
            return assignedTeachers;
        }

        public DataTable getRecordofDb(string date_assigned, string replace_teacher_id)
        {
            string query = "";
            try
            {
                query = XMLReader.readXMLtxt("checkrow", "~/DATA/XML/substituteXML.xml");
                query = query.Replace("@date_assined", date_assigned);
                query = query.Replace("@teacher_id", replace_teacher_id);

                checkedrecord = srv.Search(query);
            }
            catch (Exception e)
            {
                throw e;
            }
            return checkedrecord;
        }

        public DataTable getReplaceTeacherDetails(string replacing_teacher_id, string date_assigned)
        {
            string query = "";
            try
            {
                query = XMLReader.readXMLtxt("selectreplacingteacherdetails", "~/DATA/XML/substituteXML.xml");
                query = query.Replace("@replacing_teacher_id", replacing_teacher_id);
                query = query.Replace("@date_assigned", date_assigned);
                dtrepalcingteacherdetails = srv.Search(query);

            }
            catch (Exception e)
            {
                throw e;
            }
            return dtrepalcingteacherdetails;
        }

        public DataTable getmailID(string teacher_id)
        {
            string query = "";
            try
            {
                query = XMLReader.readXMLtxt("getmailid", "~/DATA/XML/substituteXML.xml");
                query = query.Replace("@teacher_id", teacher_id);


                mailID = srv.Search(query);
            }
            catch (Exception e)
            {
                throw e;
            }
            return mailID;

        }

        public void insertintoSubTeacherTable(string absentteacherID, string slotID, string dateAsString, string replacingteacherID, string mailID)
        {
            string query = "";
            try
            {
                query = XMLReader.readXMLtxt("insertintosubTeachermailTable", "~/DATA/XML/substituteXML.xml");
                query = query.Replace("@absent_teacher_id", absentteacherID);
                query = query.Replace("@slot_id", slotID);
                query = query.Replace("@date_assigned", dateAsString);
                query = query.Replace("@replacing_teacher_id", replacingteacherID);
                query = query.Replace("@mail_id", mailID);

                srv.Modify(query);
            }
            catch (Exception e)
            {
                throw e;

            }


        }

        public DataTable getmail(string replacing_teacher_id)
        {
            string query = "";
            try
            {
                query = XMLReader.readXMLtxt("getmailaddress", "~/DATA/XML/substituteXML.xml");
                query = query.Replace("@replacing_teacher_id", replacing_teacher_id);
                mail = srv.Search(query);
            }
            catch (Exception e)
            {
                throw e;
            }
            return mail;
        }

        public DataTable getRecordofDb(string date_assigned, string replace_teacher_id)
        {
            string query = "";
            try
            {
                query = XMLReader.readXMLtxt("checkrow", "~/DATA/XML/substituteXML.xml");
                query = query.Replace("@date_assined", date_assigned);
                query = query.Replace("@teacher_id", replace_teacher_id);

                checkedrecord = srv.Search(query);
            }
            catch (Exception e)
            {
                throw e;
            }
            return checkedrecord;
        }

        public void insertintoSubTeacherTable(string absentteacherID, string slotID, string dateAsString, string replacingteacherID, string mailID)
        {
            string query = "";
            try
            {
                query = XMLReader.readXMLtxt("insertintosubTeachermailTable", "~/DATA/XML/substituteXML.xml");
                query = query.Replace("@absent_teacher_id", absentteacherID);
                query = query.Replace("@slot_id", slotID);
                query = query.Replace("@date_assigned", dateAsString);
                query = query.Replace("@replacing_teacher_id", replacingteacherID);
                query = query.Replace("@mail_id", mailID);

                srv.Modify(query);
            }
            catch (Exception e)
            {
                throw e;

            }


        }
        public DataTable getmailID(string teacher_id)
        {
            string query = "";
            try
            {
                query = XMLReader.readXMLtxt("getmailid", "~/DATA/XML/substituteXML.xml");
                query = query.Replace("@teacher_id", teacher_id);


                mailID = srv.Search(query);
            }
            catch (Exception e)
            {
                throw e;
            }
            return mailID;

        }
        bool message = false;
        public bool getrecordsfromTable()
        {
            string query = "";
            try
            {
                query = XMLReader.readXMLtxt("getsubstitutemails", "~/DATA/XML/substituteXML.xml");



                recordsOfSubstitutemail = srv.Search(query);
                if (recordsOfSubstitutemail.Rows.Count != 0)
                {
                    message = true;
                }
                else
                {
                    message = false;
                }
            }
            catch (Exception e)
            {
                throw e;
            }
            return message;

        }

        public void createtablePresentFreeTeachers()
        {
            string query = "";
            try
            {
                query = XMLReader.readXMLtxt("presentFreeTeachers", "~/DATA/XML/substituteXML.xml");


                srv.Modify(query);
            }
            catch (Exception e)
            {
                throw e;
            }


        }

        public void insertintoPresentfreeTeachers(string teacher_id, string slot_id)
        {
            string query = "";
            try
            {
                query = XMLReader.readXMLtxt("insertintopresentTeachers", "~/DATA/XML/substituteXML.xml");
                query = query.Replace("@teacher_id", teacher_id);
                query = query.Replace("@slot_id", slot_id);
                srv.Modify(query);
            }
            catch (Exception e)
            {
                throw e;
            }
        }



        public DataTable getPresentFreeTeachers()
        {
            string query = "";
            try
            {
                query = XMLReader.readXMLtxt("presentteachersIDsandSlots", "~/DATA/XML/substituteXML.xml");

                presentTeachersIDEsandSlots = srv.Search(query);
            }
            catch (Exception e)
            {
                throw e;
            }
            return presentTeachersIDEsandSlots;
        }



        public void deletePresentfreeTeachersTable()
        {
            string query = "";
            try
            {
                query = XMLReader.readXMLtxt("deletepresentFreeTecahersTable", "~/DATA/XML/substituteXML.xml");

                srv.Modify(query);
            }
            catch (Exception e)
            {
                throw e;
            }
        }
    }
}


